
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/14/2013 00:45:20
-- Generated from EDMX file: C:\Users\vovka\Documents\Visual Studio 2012\Projects\phones\phones\Model\PhonesModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [phones];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Communities_ServiceNodes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Cities] DROP CONSTRAINT [FK_Communities_ServiceNodes];
GO
IF OBJECT_ID(N'[dbo].[FK_Contacts_Cities]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Contacts] DROP CONSTRAINT [FK_Contacts_Cities];
GO
IF OBJECT_ID(N'[dbo].[FK_Contacts_Departments]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Contacts] DROP CONSTRAINT [FK_Contacts_Departments];
GO
IF OBJECT_ID(N'[dbo].[FK_ContactsPeople_Contact]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContactsPeople] DROP CONSTRAINT [FK_ContactsPeople_Contact];
GO
IF OBJECT_ID(N'[dbo].[FK_ContactsPeople_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContactsPeople] DROP CONSTRAINT [FK_ContactsPeople_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_ContactsTelephones_Contacts]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContactsTelephones] DROP CONSTRAINT [FK_ContactsTelephones_Contacts];
GO
IF OBJECT_ID(N'[dbo].[FK_ContactsTelephones_Telephones]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ContactsTelephones] DROP CONSTRAINT [FK_ContactsTelephones_Telephones];
GO
IF OBJECT_ID(N'[dbo].[FK_Departments_Organisations]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Departments] DROP CONSTRAINT [FK_Departments_Organisations];
GO
IF OBJECT_ID(N'[dbo].[FK_PersonTelephones_Person]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PersonTelephones] DROP CONSTRAINT [FK_PersonTelephones_Person];
GO
IF OBJECT_ID(N'[dbo].[FK_PersonTelephones_Telephones]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PersonTelephones] DROP CONSTRAINT [FK_PersonTelephones_Telephones];
GO
IF OBJECT_ID(N'[dbo].[FK_Telephones_TelephoneTypes]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Telephones] DROP CONSTRAINT [FK_Telephones_TelephoneTypes];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Cities]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Cities];
GO
IF OBJECT_ID(N'[dbo].[Contacts]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Contacts];
GO
IF OBJECT_ID(N'[dbo].[ContactsPeople]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContactsPeople];
GO
IF OBJECT_ID(N'[dbo].[ContactsTelephones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ContactsTelephones];
GO
IF OBJECT_ID(N'[dbo].[Departments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Departments];
GO
IF OBJECT_ID(N'[dbo].[Organisations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Organisations];
GO
IF OBJECT_ID(N'[dbo].[People]', 'U') IS NOT NULL
    DROP TABLE [dbo].[People];
GO
IF OBJECT_ID(N'[dbo].[PersonTelephones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PersonTelephones];
GO
IF OBJECT_ID(N'[dbo].[ServiceNodes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ServiceNodes];
GO
IF OBJECT_ID(N'[dbo].[Telephones]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Telephones];
GO
IF OBJECT_ID(N'[dbo].[TelephoneTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TelephoneTypes];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Cities'
CREATE TABLE [dbo].[Cities] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NULL,
    [Code] nvarchar(10)  NULL,
    [ServiceNode_ID] int  NULL
);
GO

-- Creating table 'Contacts'
CREATE TABLE [dbo].[Contacts] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NULL,
    [City_ID] int  NULL,
    [Department_ID] int  NULL
);
GO

-- Creating table 'Departments'
CREATE TABLE [dbo].[Departments] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NULL,
    [Organisation_ID] int  NULL
);
GO

-- Creating table 'Organisations'
CREATE TABLE [dbo].[Organisations] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NULL
);
GO

-- Creating table 'People'
CREATE TABLE [dbo].[People] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [FName] nvarchar(50)  NULL,
    [MName] nvarchar(50)  NULL,
    [LName] nvarchar(50)  NULL
);
GO

-- Creating table 'ServiceNodes'
CREATE TABLE [dbo].[ServiceNodes] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NULL
);
GO

-- Creating table 'Telephones'
CREATE TABLE [dbo].[Telephones] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Number] nvarchar(50)  NULL,
    [TelephoneType_ID] int  NULL
);
GO

-- Creating table 'TelephoneTypes'
CREATE TABLE [dbo].[TelephoneTypes] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NULL
);
GO

-- Creating table 'ContactsTelephones'
CREATE TABLE [dbo].[ContactsTelephones] (
    [Contacts_ID] int  NOT NULL,
    [Telephones_ID] int  NOT NULL
);
GO

-- Creating table 'PersonTelephones'
CREATE TABLE [dbo].[PersonTelephones] (
    [People_ID] int  NOT NULL,
    [Telephones_ID] int  NOT NULL
);
GO

-- Creating table 'ContactsPeople'
CREATE TABLE [dbo].[ContactsPeople] (
    [Contacts_ID] int  NOT NULL,
    [People_ID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Cities'
ALTER TABLE [dbo].[Cities]
ADD CONSTRAINT [PK_Cities]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Contacts'
ALTER TABLE [dbo].[Contacts]
ADD CONSTRAINT [PK_Contacts]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Departments'
ALTER TABLE [dbo].[Departments]
ADD CONSTRAINT [PK_Departments]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Organisations'
ALTER TABLE [dbo].[Organisations]
ADD CONSTRAINT [PK_Organisations]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'People'
ALTER TABLE [dbo].[People]
ADD CONSTRAINT [PK_People]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ServiceNodes'
ALTER TABLE [dbo].[ServiceNodes]
ADD CONSTRAINT [PK_ServiceNodes]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Telephones'
ALTER TABLE [dbo].[Telephones]
ADD CONSTRAINT [PK_Telephones]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'TelephoneTypes'
ALTER TABLE [dbo].[TelephoneTypes]
ADD CONSTRAINT [PK_TelephoneTypes]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [Contacts_ID], [Telephones_ID] in table 'ContactsTelephones'
ALTER TABLE [dbo].[ContactsTelephones]
ADD CONSTRAINT [PK_ContactsTelephones]
    PRIMARY KEY NONCLUSTERED ([Contacts_ID], [Telephones_ID] ASC);
GO

-- Creating primary key on [People_ID], [Telephones_ID] in table 'PersonTelephones'
ALTER TABLE [dbo].[PersonTelephones]
ADD CONSTRAINT [PK_PersonTelephones]
    PRIMARY KEY NONCLUSTERED ([People_ID], [Telephones_ID] ASC);
GO

-- Creating primary key on [Contacts_ID], [People_ID] in table 'ContactsPeople'
ALTER TABLE [dbo].[ContactsPeople]
ADD CONSTRAINT [PK_ContactsPeople]
    PRIMARY KEY NONCLUSTERED ([Contacts_ID], [People_ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ServiceNode_ID] in table 'Cities'
ALTER TABLE [dbo].[Cities]
ADD CONSTRAINT [FK_Communities_ServiceNodes]
    FOREIGN KEY ([ServiceNode_ID])
    REFERENCES [dbo].[ServiceNodes]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Communities_ServiceNodes'
CREATE INDEX [IX_FK_Communities_ServiceNodes]
ON [dbo].[Cities]
    ([ServiceNode_ID]);
GO

-- Creating foreign key on [City_ID] in table 'Contacts'
ALTER TABLE [dbo].[Contacts]
ADD CONSTRAINT [FK_Contacts_Cities]
    FOREIGN KEY ([City_ID])
    REFERENCES [dbo].[Cities]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Contacts_Cities'
CREATE INDEX [IX_FK_Contacts_Cities]
ON [dbo].[Contacts]
    ([City_ID]);
GO

-- Creating foreign key on [Department_ID] in table 'Contacts'
ALTER TABLE [dbo].[Contacts]
ADD CONSTRAINT [FK_Contacts_Departments]
    FOREIGN KEY ([Department_ID])
    REFERENCES [dbo].[Departments]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Contacts_Departments'
CREATE INDEX [IX_FK_Contacts_Departments]
ON [dbo].[Contacts]
    ([Department_ID]);
GO

-- Creating foreign key on [Organisation_ID] in table 'Departments'
ALTER TABLE [dbo].[Departments]
ADD CONSTRAINT [FK_Departments_Organisations]
    FOREIGN KEY ([Organisation_ID])
    REFERENCES [dbo].[Organisations]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Departments_Organisations'
CREATE INDEX [IX_FK_Departments_Organisations]
ON [dbo].[Departments]
    ([Organisation_ID]);
GO

-- Creating foreign key on [TelephoneType_ID] in table 'Telephones'
ALTER TABLE [dbo].[Telephones]
ADD CONSTRAINT [FK_Telephones_TelephoneTypes]
    FOREIGN KEY ([TelephoneType_ID])
    REFERENCES [dbo].[TelephoneTypes]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Telephones_TelephoneTypes'
CREATE INDEX [IX_FK_Telephones_TelephoneTypes]
ON [dbo].[Telephones]
    ([TelephoneType_ID]);
GO

-- Creating foreign key on [Contacts_ID] in table 'ContactsTelephones'
ALTER TABLE [dbo].[ContactsTelephones]
ADD CONSTRAINT [FK_ContactsTelephones_Contacts]
    FOREIGN KEY ([Contacts_ID])
    REFERENCES [dbo].[Contacts]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Telephones_ID] in table 'ContactsTelephones'
ALTER TABLE [dbo].[ContactsTelephones]
ADD CONSTRAINT [FK_ContactsTelephones_Telephones]
    FOREIGN KEY ([Telephones_ID])
    REFERENCES [dbo].[Telephones]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContactsTelephones_Telephones'
CREATE INDEX [IX_FK_ContactsTelephones_Telephones]
ON [dbo].[ContactsTelephones]
    ([Telephones_ID]);
GO

-- Creating foreign key on [People_ID] in table 'PersonTelephones'
ALTER TABLE [dbo].[PersonTelephones]
ADD CONSTRAINT [FK_PersonTelephones_Person]
    FOREIGN KEY ([People_ID])
    REFERENCES [dbo].[People]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Telephones_ID] in table 'PersonTelephones'
ALTER TABLE [dbo].[PersonTelephones]
ADD CONSTRAINT [FK_PersonTelephones_Telephones]
    FOREIGN KEY ([Telephones_ID])
    REFERENCES [dbo].[Telephones]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PersonTelephones_Telephones'
CREATE INDEX [IX_FK_PersonTelephones_Telephones]
ON [dbo].[PersonTelephones]
    ([Telephones_ID]);
GO

-- Creating foreign key on [Contacts_ID] in table 'ContactsPeople'
ALTER TABLE [dbo].[ContactsPeople]
ADD CONSTRAINT [FK_ContactsPeople_Contact]
    FOREIGN KEY ([Contacts_ID])
    REFERENCES [dbo].[Contacts]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [People_ID] in table 'ContactsPeople'
ALTER TABLE [dbo].[ContactsPeople]
ADD CONSTRAINT [FK_ContactsPeople_Person]
    FOREIGN KEY ([People_ID])
    REFERENCES [dbo].[People]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ContactsPeople_Person'
CREATE INDEX [IX_FK_ContactsPeople_Person]
ON [dbo].[ContactsPeople]
    ([People_ID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------